package cn.gson.zuche.model.dao;import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import cn.gson.zuche.model.GlobalParamHandler;
import cn.gson.zuche.model.bean.Cartype;
import cn.gson.zuche.model.bean.Cartypee;
import cn.gson.zuche.model.jdbc.MySqlDb;
import cn.gson.zuche.model.jdbc.ParamHandler;
import cn.gson.zuche.model.jdbc.ResultHandler;

public class CartypeDao implements ResultHandler<Cartype>{
		
	private MySqlDb db = MySqlDb.getInstance();
	/**
	 * 通过车名字获取车的所有信息
	 * @return
	 * @throws SQLException
	 */
	public List<Cartype> selectbyname(String id) throws SQLException{
		String sql="select * from car_type where car_type_id="+id+"";	
		return db.executeQuery(sql, this);
	}
	
	/**
	 * 通过车id查找此类车的详细情况
	 * @return
	 * @throws SQLException
	 */
	public List<Cartype> selectchexiangxi(String typeid) throws SQLException{
		String sql="select car_license_id,car_type_name,car_zt_name from car_type,car,car_zt where car_type_ids=car_type_id and car_zt_id=car_zt_ids and car_type_ids='"+typeid+"'";	
		return db.executeQuery(sql, this);
	}
	/**
	 * 通过车型获取此车型所有的车
	 */
	public List<Cartype> selectbychexing(String name,String id) throws SQLException{
		String sql="select order_id,car_license_id,car_type_name,car_zt_name from car_type,car,car_zt,user_order where order_id='"+id+"' and car_type_ids=car_type_id and car_zt_id=car_zt_ids and car_type_ids=(SELECT car_type_id from car_type where car_type_name='"+name+"')";	
		return db.executeQuery(sql, this);
	}
	public List<Cartype> shuaixin(String name) throws SQLException{
		String sql="select car_license_id,car_type_name,car_zt_name from car_type,car,car_zt where car_type_ids=car_type_id and car_zt_id=car_zt_ids and car_type_ids=(SELECT car_type_id from car_type where car_type_name='"+name+"')";	
		return db.executeQuery(sql, this);
	}
	/**
	 * 通过id更新库存
	 * @return
	 * @throws SQLException
	 */
	public boolean updatakucun(String id,String count) throws SQLException{
			String sql="UPDATE car_type SET car_kucun="+count+" WHERE car_type_id='"+id+"'";
			return db.executeUpdate(sql)>0;
	}
	public boolean huankucun(String chepaihao,String count) throws SQLException{
		String sql="UPDATE car_type SET car_kucun="+count+" WHERE car_type_id=(select car_type_ids from car where car_license_id='"+chepaihao+"')";
		return db.executeUpdate(sql)>0;
}
	/**
	 * 获取新车的id
	 * @author Seven
	 *
	 */
	public List<Cartype> huoquxincheid(String name) throws SQLException{
		String sql="select car_type_id from car_type where car_type_name='"+name+"'";	
		return db.executeQuery(sql, this);
	}
	/**
	 * 插入车的描述
	 */
	public boolean updatades(String id,String nr) throws SQLException{
		String sql="UPDATE car_type SET car_des='"+nr+"' WHERE car_type_id='"+id+"'";
		return db.executeUpdate(sql)>0;
}
	/**
	 * 增添新车
	 * @return
	 * @throws SQLException
	 */
	public boolean insertxinche(final Cartype type) throws SQLException{
		String sql="INSERT into car_type(car_type_name,car_price,car_seat_num,car_door_num,car_oil,car_bsx,car_pail,car_drive,car_oil_box,car_gps,car_sky_window,car_gasbag,car_backld,car_xinche) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		return db.executeUpdate(sql,new save(type))>0;
}
	
	 
	public class save implements ParamHandler{
		Cartype t;
		public save(Cartype t){
			this.t=t;
		}
		public void doHander(PreparedStatement pStatement) throws SQLException {
			pStatement.setString(1, t.getCarTypeName());
			pStatement.setString(2, t.getCarPrice());
			pStatement.setInt(3, t.getCarSeatNum());
			pStatement.setInt(4, t.getCarDoorNum());
			pStatement.setString(5, t.getCarOil());
			pStatement.setString(6, t.getCarBsx());
			pStatement.setString(7, t.getCarPail());
			pStatement.setString(8, t.getCarDrive());
			pStatement.setString(9, t.getCarOilBox());
			pStatement.setString(10, t.getCarGps());
			pStatement.setString(11, t.getCarSkyWindow());
			pStatement.setString(12, t.getCarGasbag());
			pStatement.setString(13, t.getCarBackld());
			pStatement.setString(14, t.getCarXinche());
			
		}
		
	}
	
	/**
	 * 更新车的信息
	 * @return
	 * @throws SQLException
	 */
	public boolean updatachexinxi(final Cartype t) throws SQLException{
		String sql="update car_type  SET car_type_name=?,car_price=?,car_seat_num=?,car_door_num=?,car_oil=?,car_bsx=?,car_pail=?,car_drive=?,car_oil_box=?,car_gps=?,car_sky_window=?,car_gasbag=? WHERE car_type_id=?";
		return db.executeUpdate(sql,new GlobalParamHandler(t.getCarTypeName(),t.getCarPrice(),t.getCarSeatNum(),t.getCarDoorNum(),t.getCarOil(),t.getCarBsx(),t.getCarPail(),t.getCarDrive(),t.getCarOilBox(),t.getCarGps(),t.getCarSkyWindow(),t.getCarGasbag(),t.getCarTypeId()))>0;
	}
	
	/**
	 *分配车时更新库存
	 * @return
	 * @throws SQLException
	 */
	
	public boolean fcgaikucun(String name,Integer count) throws SQLException{
		String sql="UPDATE car_type SET car_kucun="+count+" WHERE car_type_name='"+name+"'";
		return db.executeUpdate(sql)>0;
}
	/**
	 * 增减销量
	 * @return
	 * @throws SQLException
	 */
	public boolean zengjiaxiaoliang(String name,Integer count) throws SQLException{
		String sql="UPDATE car_type SET car_xiaoliang='"+count+"' WHERE car_type_name='"+name+"'";
		return db.executeUpdate(sql)>0;
}
	/**
	 * 查找销量
	 * @return
	 * @throws SQLException
	 */
	public List<Cartype> selectxiaoliang(String name) throws SQLException{
		String sql="SELECT car_xiaoliang from car_type where car_type_name='"+name+"'";	
		return db.executeQuery(sql, this);
	}
	
	
	
	public List<Cartype> selectsuoyou() throws SQLException{
		String sql="SELECT car_type_id,car_type_name,car_price,car_xinche,car_xinxi,car_kucun from car_type";	
		return db.executeQuery(sql, this);
	}
	public List<Cartype> findPinpai() throws SQLException {
		String sql = "select car_pinpai from car_type";
		return db.executeQuery(sql, this);
	}	

	
	public List<Cartype> findxinxi(String name) throws SQLException {
		String sql = "select * from car_type where car_type_name='"+name+"'";
		return db.executeQuery(sql,this);
	}
	public boolean updata(String id,String chexing) throws SQLException{
		String sql="update car_type  SET car_xinche='"+chexing+"' WHERE car_type_id='"+id+"'";
		return db.executeUpdate(sql)>0;
	}
	public boolean delete(String id) throws SQLException{
		String sql="delete from car_type where car_type_id="+id+"";
		return db.executeUpdate(sql)>0;

	}
	public List<Cartype> findXinxi(String chexing,String jiage,String max,String pinpai,String paixu) throws SQLException {
		StringBuffer sql=new StringBuffer();
		StringBuffer sql1=sql.append("select car_type_name,car_price,car_image_url,car_xinxi,car_xinche,car_remen from car_type where 1=1 and car_kucun>0 ");
		if(chexing!=""){
			sql1.append("and car_xing='").append(chexing).append("'");
		}
		if(jiage!=""){
			sql1.append("and car_price between'").append(jiage).append("'and '").append(max).append("'");
		}
		if(pinpai!=null){
			sql1.append("and car_pinpai in ('").append(pinpai).append("')");
		}
		if(paixu!=null){
			if(paixu.equalsIgnoreCase("asc")){	
				sql1.append("order by car_price ").append(paixu);
			}else{
				sql1.append("order by car_xiaoliang ").append(paixu);
			}
		}
		String sqls=sql1.toString();
		
		return db.executeQuery(sqls,this);
	}
	
	public List<Cartype> findche() throws SQLException{
	String sql=" select car_type_name,car_price,car_image_url,car_xinxi,car_xinche,car_remen  from car_type where car_kucun>0";	
	return db.executeQuery(sql,this);
		}
	
	public List<Cartype> carOrder(String mess) throws SQLException{
		String sql="select car_type_name,car_price,car_image_url,car_xinxi FROM car_type ORDER BY "+mess;	
		return db.executeQuery(sql,this);
			}
	
	public List<Cartype> carOrderDesc(String mess) throws SQLException{
		String sql="select car_type_name,car_price,car_image_url,car_xinxi FROM car_type ORDER BY "+mess+" DESC ";	
		return db.executeQuery(sql,this);
			}
	
	
	
	
	@Override
	public Cartype doHander(Map<String, Object> row) {
		Cartype xche=new Cartype();	
		xche.setCarTypeId((Integer)( row.get("car_type_id")));
		xche.setCarTypeName((String)row.get("car_type_name"));
		xche.setCarPrice((String)row.get("car_price"));
		if(row.get("car_seat_num")!=null){
			xche.setCarSeatNum(Integer.parseInt(row.get("car_seat_num")+""));
		}
		
		xche.setCarDoorNum((Integer)row.get("car_door_num"));
		xche.setCarOil((String)row.get("car_oil"));
		xche.setCarBsx((String)row.get("car_bsx"));
		xche.setCarPail((String)row.get("car_pail"));
		xche.setCarDrive((String)row.get("car_drive"));
		xche.setCarOilBox((String)row.get("car_oil_box"));
		xche.setCarGps((String)row.get("car_gps"));
		xche.setCarSkyWindow((String)row.get("car_sky_window"));
		xche.setCarBsx((String)row.get("car_bsx"));
		xche.setCarBackld((String)row.get("car_backld"));
		xche.setCarImageUrl((String)row.get("car_image_url"));
		xche.setCarPinpai((String)row.get("car_pinpai"));
		xche.setCarXinxi((String)row.get("car_xinxi"));
		xche.setCarXinche((String)row.get("car_xinche"));
		xche.setCarRemen((String)row.get("car_remen"));
		xche.setCarXiaoliang((Integer)row.get("car_xiaoliang"));
		xche.setCarZtId((Integer)row.get("car_zt_id"));
		xche.setCarZtName((String)row.get("car_zt_name"));
		xche.setCarZtIds((Integer)row.get("car_zt_ids"));
		xche.setCarTypeIds((Integer)row.get("car_type_ids"));
		xche.setCarDv((String)row.get("car_dv"));
		xche.setNum(row.get("car_num")+"");
		xche.setCarlicenseid((String) row.get("car_license_id"));
		xche.setCardes((String) row.get("car_des"));
		xche.setCarkucun(row.get("car_kucun")+"");
		xche.setCarGasbag((String) row.get("car_gasbag"));
		xche.setDindan((String) row.get("order_id"));
		return xche;
	}

	public List<Cartype> findchexinxi(String carname) throws SQLException{
		String sql=" select car_type_name,car_price,car_image_url,car_xinxi,car_xinche,car_remen,car_xing  from car_type where car_kucun>0 and car_type_name="+carname+"";	
		return db.executeQuery(sql,this);
	}

	public List<Cartype> findallbyname (String cartypename) throws SQLException{
		String sql = "SELECT * FROM car_type WHERE car_type_name = '"+cartypename+"'";
		return db.executeQuery(sql, this);
	}
	

}
